﻿// Author: Dragos Plesca
// Project: DatabaseWork
// Path: C:\Users\Tym\Documents\Visual Studio 2008\Projects\WebCrawlerProject\ClassLibrary1
// Creation date: 1/11/2009 1:07 PM
// Last modified: 1/11/2009 2:11 PM

#region Using directives
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Error;
using System.Diagnostics;
#endregion

namespace Database
{
    /// <summary>
    /// Database work
    /// </summary>
    public class DatabaseWork:IDisposable
    {
        /// <summary>
        /// _conexiune
        /// </summary>
        public SqlConnection _conexiune;

        /// <summary>
        /// Create database work
        /// </summary>
        public DatabaseWork()
        {
            _conexiune = new SqlConnection(@"Data Source=DRAGOS\SQLEXPRESS;Initial Catalog=crawler;Persist Security Info=True;User ID=dragos;Password=ratat");
           OpenConnection();
        } // DatabaseWork()

        /// <summary>
        /// Open connection
        /// </summary>
        protected void OpenConnection()
        {
            try
            {
                _conexiune.Open();
                //Console.WriteLine("We have succesfully connected...");
            }
            catch (Exception)
            {
                //Console.WriteLine(e.Message);
                ErrorManagement.ManageException(new Exception("Realizarea conexiunii cu baza de date esuata"));
            }
        } // OpenConnection()
        /// <summary>
        /// Close connection
        /// </summary>
        protected void CloseConnection()
        {
            try
            {
                _conexiune.Close();
                //Console.WriteLine("We have succesfully disconnected...");
            }
            catch (Exception)
            {
               
                ErrorManagement.ManageException(new Exception("Realizarea deconectarii de la baza de date esuata"));
            }
        } // CloseConnection()

        /// <summary>
        /// Add page
        /// </summary>
        public void AddPage(string pageLink,  string lastModifiedDate)
        {
            SqlDataReader myReader = null;
            try
            {
                
                SqlCommand myCommand = new SqlCommand("INSERT INTO dbo.WebPage (link, last_update) " +
                                         "Values ('" +pageLink + "', '"  + lastModifiedDate + "')", _conexiune);
                myReader = myCommand.ExecuteReader();
                if (myReader.RecordsAffected == 0)
                    Debug.WriteLine("Insert failed.");

            } // try
            catch (Exception e)
            {
                //Console.WriteLine(e.Message);
                ErrorManagement.ManageException(new Exception("Failed to insert "+pageLink));
            }
            finally
            {
                myReader.Close();
            }
        } // AddPage(pagina)
        
        /// <summary>
        /// Update page
        /// </summary>
        /// <param name="pagina">Pagina</param>
        public void UpdatePage(string pageLink,  string lastModifiedDate)
        {
            SqlDataReader myReader = null;
            try
            {
                
                SqlCommand myCommand = new SqlCommand("UPDATE dbo.WebPage set link_content = '" + (string)pageLink + "', last_update =  '" + lastModifiedDate + "' where link = '" +pageLink + "'", _conexiune);
                myReader = myCommand.ExecuteReader();
                if (myReader.RecordsAffected == 0)
                    Debug.WriteLine("Update failed.");

            } // try
            catch (Exception e)
            {
                //Console.WriteLine(e.Message);
                ErrorManagement.ManageException(new Exception("Updatarea bazei de date esuata"));
            }
            finally
            {
                myReader.Close();
            }
        } // UpdatePage(pagina)

        /// <summary>
        /// Add seed
        /// </summary>
        /// <param name="seed">Seed</param>
        public void AddSeed(string seed)
        {
            SqlDataReader myReader = null;
            try
            {
                
                SqlCommand myCommand = new SqlCommand("INSERT dbo.Seed(seed) VALUES ('" + seed + "')", _conexiune);

                myReader = myCommand.ExecuteReader();
                //if (myReader.RecordsAffected == 0)
                    //Console.WriteLine("Insert failed.");

            } // try
            catch (Exception)
            {
                //Console.WriteLine(e.Message);
                ErrorManagement.ManageException(new Exception("Adaugare link in baza initiala esuata"));
            }
            finally
            {
                myReader.Close();
            }
        } // AddSeed(seed)
        /// <summary>
        /// Get seed
        /// </summary>
        public string GetSeed(int id)
        {
            SqlDataReader myReader = null;
            string seed = null;
            try
            {
                
                SqlCommand myCommand = new SqlCommand("SELECT seed from dbo.Seed where id=" + id, _conexiune);
                myReader = myCommand.ExecuteReader();
                myReader.Read();
                seed = (string)myReader[0];
            }
            catch (Exception e)
            {
                ErrorManagement.AppendException(e);
            }
            finally
            {
                myReader.Close();               
            }
            return seed;
        } // GetSeed()

        /// <summary>
        /// Count seed
        /// </summary>
        public int CountSeed()
        {
            SqlDataReader myReader = null;
            int returnValue = 0;
            try
            {

                SqlCommand myCommand = new SqlCommand("SELECT count(seed) from dbo.Seed", _conexiune);
                myReader = myCommand.ExecuteReader();
                myReader.Read();
                returnValue = (int)myReader[0];
            }
            catch (Exception exc)
            {
                ErrorManagement.ManageException(new Exception("Numararea linkurilor din baza initiala esuata"));
            }
            finally
            {
                myReader.Close();
            }
            return returnValue;
        } // CountSeed()

        /// <summary>
        /// Page exists
        /// </summary>
        public bool PageExists(string link)
        {
            SqlDataReader myReader = null;
            try
            {
                SqlCommand myCommand = new SqlCommand("SELECT id from dbo.WebPage where link = '" + link + "'", _conexiune);
                myReader = myCommand.ExecuteReader();
                if (myReader.HasRows)
                    return true;
            }
            catch (Exception)
            {
                ErrorManagement.ManageException(new Exception("Selectarea paginii "+link+" in baza de date a esuat"));
            }
            finally
            {
                myReader.Close();               
            }
            return false;
   
        } // PageExists(link)

        /// <summary>
        /// Is modified
        /// </summary>
        public bool isModified(DateTime data, string link)
        {
            SqlDataReader myReader = null;
            try
            {
                SqlCommand myCommand = new SqlCommand("SELECT last_update from dbo.WebPage where link = '" + link + "'", _conexiune);
                myReader = myCommand.ExecuteReader();
                if (myReader.HasRows)
                {
                    myReader.Read();
                    if(data.ToString().Equals((string)myReader[0]))
                        return true;
                }
            } // try
            catch (Exception e)
            {
                ErrorManagement.AppendException(e);
            }
            finally
            {
                myReader.Close();

            }
            return false;
        } // isModified(data, link)

        /// <summary>
        /// Robot exists
        /// </summary>
        public bool RobotExists(string link)
        {
            SqlDataReader myReader = null;
            try
            {
                SqlCommand myCommand = new SqlCommand("SELECT id from dbo.Robots where link = '" + link + "'", _conexiune);
                myReader = myCommand.ExecuteReader();
                if (myReader.HasRows)
                    return true;
            }
            catch (Exception e)
            {
                ErrorManagement.AppendException(e);
            }
            finally
            {
                myReader.Close();
            }
            return false; 
        } // RobotExists(link)

        /// <summary>
        /// Add robot
        /// </summary>
        public void AddRobot(string link, string content, string last_modified)
        {
            SqlDataReader myReader = null;
            try
            {
                SqlCommand myCommand = new SqlCommand("INSERT into dbo.Robots(link, robot_content, last_modified) values ('" + link + "', '" + content + "', '"+ last_modified +"')", _conexiune);
                myReader = myCommand.ExecuteReader();
                if (myReader.RecordsAffected == 0)
                    Console.WriteLine("Insert failed.");
            }
            catch (Exception e)
            {
                ErrorManagement.AppendException(e);
            }
            finally
            {
                myReader.Close();
            }// finally            
        } // AddRobot(link, content, last_modified)

        /// <summary>
        /// Robot is modified 
        /// </summary>
        /// <param name="data">Data</param>
        /// <param name="link">Link</param>
        public bool RobotisModified(DateTime data, string link)
        {
            SqlDataReader myReader = null;
            try
            {
                SqlCommand myCommand = new SqlCommand("SELECT last_modified from dbo.Robots where link = '" + link + "'", _conexiune);
                myReader = myCommand.ExecuteReader();
                if (myReader.HasRows)
                {
                    myReader.Read();
                    if (data.ToString().Equals((string)myReader[0]))
                        return true;
                }
            } // try
            catch (Exception e)
            {
                ErrorManagement.AppendException(e);
            }
            finally
            {
                myReader.Close();
            }
            return false;
        } // RobotisModified(data, link)

        /// <summary>
        /// Update robots
        /// </summary>
        /// <param name="link">Link</param>
        /// <param name="content">Content</param>
        public void UpdateRobots(string link, string content)
        {
            SqlDataReader myReader = null;
            try
            {
                
                SqlCommand myCommand = new SqlCommand("UPDATE dbo.Robots set robot_content = '" + (string)content + "', last_modified =  '" + DateTime.Now.ToLocalTime() + "' where link = '" + link + "'", _conexiune);
                myReader = myCommand.ExecuteReader();
                if (myReader.RecordsAffected == 0)
                    Console.WriteLine("Update failed.");

            } // try
            catch (Exception e)
            {
                //Console.WriteLine(e.Message);
                ErrorManagement.AppendException(e);
            }
            finally
            {
                myReader.Close();
            }
        } // UpdateRobots(link, content)

        public string GetRobotsContent(string link)
        {
            SqlDataReader myReader = null;
            string robot = null;
            try
            {
                SqlCommand myCommand = new SqlCommand("SELECT robot_content from dbo.Robots where link=" + link, _conexiune);
                myReader = myCommand.ExecuteReader();
                myReader.Read();
                robot = (string)myReader[0];
            }
            catch (Exception e)
            {
                ErrorManagement.AppendException(e);
            }
            finally
            {
                myReader.Close();
            }
            return robot;
        }




        #region IDisposable Members

        public void Dispose()
        {
            CloseConnection();
        }

        #endregion
    } // class DatabaseWork
} // namespace Database
